require 'active_record/vendor/mysql'
require 'FasterCSV'

class CsvToTable 

        def get_database_connection(database)
		conn = Mysql.new('localhost', 'root','', "#{database}")	
		return conn
		#rescue
		#     raise "This database does not exist"
	end	

        def get_number_of_columns_for_csv_file(filename)
		row_counter = 0
		@no_of_fields = []
		FasterCSV.foreach(filename) do |row|
				@no_of_fields[row_counter] = row.size
				row_counter+=1
		end
		return @no_of_fields.sort[@no_of_fields.size-1]	
	end

	def match_column_count(filename,tablename,database)
		@no_of_fields_in_a_file = get_number_of_columns_for_csv_file(filename)
		new_conn = get_database_connection(database)
		result = new_conn.query("select * from #{tablename}").num_fields()
		new_conn.close()
		if result == @no_of_fields_in_a_file
			puts "The number of columns are same"
			return true
		else
			puts "The number of columns are different"
			puts
			puts "The number of columns in a CSV file are: #{@no_of_fields_in_a_file}"
			puts
			puts "The number of columns in the table are: #{result}"
			puts
			list_missing_or_null_columns_for_csv(filename, database,tablename)
			return false
		end	
		
	end

        def list_missing_or_null_columns_for_csv(filename, database,tablename)
		row_counter = 0
		puts "The null value report of CVS file:"
		FasterCSV.foreach(filename) do |row|
		row_counter+=1
		for field_value in 0..row.size-1
				if row[field_value].nil?
					puts "for #{row_counter}th row, the value of #{field_value+1} field is null"
				end	
			end
		end
		puts
	end	

        def entry_method_for_process_to_start(filename,tablename,database)
		if filename.class == 'Array'
			if tablename.class != 'Array' || (filename.size != tablename.size)
				puts "No of tables do not match the number of CSV files" 
			else 
				for file_counter in 0..filename.size
					load_csv_to_table(filename[file_counter],tablename[file_counter],database)
				end
			end
		else
			load_csv_to_table(filename,tablename,database)		
		end	
	end	

	def load_csv_to_table(filename, tablename, database)
		row_counter = 0
		if match_column_count(filename, tablename, database)
			new_conn = get_database_connection(database)
			@start_time_loading = Time.now
			FasterCSV.foreach(filename) do |row|
				if row.size!=0
					@connected_row= row.join('\',\'')
					@connected_row<<'\''
					@connected_row[0] = '\'' << @connected_row[0]
					@spacified_row = @connected_row.gsub(',,',',nil,')
					if !@spacified_row.nil?	
						@connected_row = @spacified_row
					end	
					new_conn.query("Insert into box values(#{@connected_row})")
				end
				row_counter = row_counter + 1
				@time_diff = Time.now - @start_time_loading
			end	
			puts "Total number of records loaded #{row_counter}"
			puts "The time required for loading #{@time_diff} seconds" 
			puts "Average loading time per record was #{(@time_diff)/row_counter}"
			puts "Data was succesfully loaded."
		else
			puts "Data could not be loaded."
		end			
	end
end

new_csv = CsvToTable.new
new_csv.entry_method_for_process_to_start(["box.csv","excel_box.csv"],["box","excel_box"],"summer_development")

	
